<?php

class m_pengadaan extends CI_Model {

    function __construct() {
        // Call the Model constructor
        parent::__construct();
    }

    function get_count_all_pengadaan() {
        $sql = "SELECT COUNT(bagian_cd)'total' FROM pengadaan";
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return 0;
        }
    }

    function get_count_all_pengadaan_search($params) {
        $sql = "SELECT COUNT(bagian_cd)'total', LEFT(pengadaan_date, 4)'tahun', MID(pengadaan_date, 6, 2)'bulan'
                FROM pengadaan
                WHERE bagian_cd LIKE ? 
                AND LEFT(pengadaan_date, 4) LIKE ? 
                AND MID(pengadaan_date, 6, 2) LIKE ? 
                ";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return 0;
        }
    }

    // get user profil
    function get_all_pengadaan_pagination($params) {
        $sql = "SELECT a.*, b.bagian_name FROM pengadaan a
                JOIN bagian b ON a.bagian_cd = b.bagian_cd
                ORDER BY a.pengadaan_id DESC
                LIMIT ?, ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    function get_all_pengadaan_pagination_search($params) {
        $sql = "SELECT a.*, b.bagian_name FROM pengadaan a
                JOIN bagian b ON a.bagian_cd = b.bagian_cd
                WHERE a.bagian_cd LIKE ? 
                AND LEFT(a.pengadaan_date, 4) LIKE ? 
                AND MID(a.pengadaan_date, 6, 2) LIKE ?                 
                ORDER BY a.pengadaan_id DESC
                LIMIT ?, ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    function get_all_pengadaan() {
        $sql = "SELECT * FROM pengadaan ORDER BY pengadaan_id";
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    function get_pengadaan_by_pengadaan_id($params) {
        $sql = "SELECT a.*,b.bagian_name, b.bagian_penanggungjawab FROM pengadaan a
                JOIN bagian b ON a.bagian_cd = b.bagian_cd
                WHERE a.pengadaan_id = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    function get_pengadaan_by_pengadaan_nomor($params) {
        $sql = "SELECT * FROM pengadaan WHERE pengadaan_nomor = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    function get_pengadaan_by_pengadaan_ba_dist($params) {
        $sql = "SELECT * FROM pengadaan WHERE pengadaan_ba_dist = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    function update_pengadaan_att_surat_by_pengadaan_id($pengadaan_id) {
        $sql = "UPDATE pengadaan SET pengadaan_att_surat = ? WHERE pengadaan_id = ?";
        return $this->db->query($sql, $pengadaan_id);
    }

    function update_pengadaan_att_dist_by_pengadaan_id($pengadaan_id) {
        $sql = "UPDATE pengadaan SET pengadaan_att_dist = ? WHERE pengadaan_id = ?";
        return $this->db->query($sql, $pengadaan_id);
    }

    function update_pengadaan_status_generate_by_pengadaan_id($pengadaan_id) {
        $sql = "UPDATE pengadaan SET pengadaan_generate = ? WHERE pengadaan_id = ?";
        return $this->db->query($sql, $pengadaan_id);
    }

    function get_last_pengadaan_id() {
        $sql = "SELECT pengadaan_id FROM pengadaan ORDER BY pengadaan_id DESC LIMIT 0,1";
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['pengadaan_id'];
        } else {
            return "";
        }
    }

    // insert
    function insert($params) {
        // update by this date
        $sql = "INSERT INTO pengadaan(pengadaan_nomor, pengadaan_date, bagian_cd, 
                                      pengadaan_ba_dist, pengadaan_tgl_dist, pengadaan_keterangan, 
                                      pengadaan_att_surat, pengadaan_att_dist, pengadaan_generate, 
                                      mdb, mdd) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW() )";
        return $this->db->query($sql, $params);
    }

    // edit
    function edit($params) {
        // update by this date
        $sql = "UPDATE pengadaan 
                SET pengadaan_nomor = ?, 
                pengadaan_date = ?, 
                bagian_cd = ?, 
                pengadaan_keterangan = ?, 
                pengadaan_att_surat = ?,
                mdb = ? 
                WHERE pengadaan_id = ?";
        return $this->db->query($sql, $params);
    }

    // delete
    function delete($params) {
        // update by this date
        $sql = "DELETE FROM pengadaan WHERE pengadaan_id = ?";
        return $this->db->query($sql, $params);
    }

    function distribusi($params) {
        // update by this date
        $sql = "UPDATE pengadaan SET pengadaan_ba_dist = ?, pengadaan_tgl_dist = ?, mdb = ?  WHERE pengadaan_id = ?";
        return $this->db->query($sql, $params);
    }

    function get_count_detail_pengajuan_barang_pagination_by_pengadaan_id($params) {
        $sql = "SELECT COUNT(b.barang_id) 'total'
                FROM pengadaan a JOIN barang b ON a.pengadaan_id = b.pengadaan_id 
                LEFT JOIN ruang c ON c.ruang_cd = b.ruang_cd
                WHERE a.pengadaan_id = ?
                ORDER BY b.barang_noinventaris ASC";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return 0;
        }
    }
    
    function get_detail_pengajuan_barang_pagination_by_pengadaan_id($params) {
        $sql = "SELECT b.*, c.ruang_nomor
                FROM pengadaan a JOIN barang b ON a.pengadaan_id = b.pengadaan_id 
                LEFT JOIN ruang c ON c.ruang_cd = b.ruang_cd
                WHERE a.pengadaan_id = ?
                ORDER BY b.barang_id ASC
                LIMIT ?, ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }
    
    function get_all_pengajuan_barang_by_pengadaan_id($params) {
        $sql = "SELECT b.*, c.ruang_nomor
                FROM pengadaan a JOIN barang b ON a.pengadaan_id = b.pengadaan_id 
                LEFT JOIN ruang c ON c.ruang_cd = b.ruang_cd
                WHERE a.pengadaan_id = ?
                ORDER BY b.barang_id ASC";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }
    
    function set_detail_distribusi($params) {
        // update by this date
        $sql = "UPDATE barang 
                SET barang_nama = ?,
                barang_umurekonomis = ?,
                barang_tglbeli = ?,
                barang_harga_beli = ?,
                barang_nilai_buku = ?,
                ruang_cd = ?
                WHERE barang_id = ?";
        return $this->db->query($sql, $params);
    }
    
    function set_update_serah_terima($params) {
        $sql = "UPDATE pengadaan
                SET pengadaan_ba_dist = ?,
                pengadaan_tgl_dist = ?,
                pengadaan_att_dist = ?
                WHERE pengadaan_id = ?";
        return $this->db->query($sql, $params);
    }
    
    function get_nomor_serah_terima($params) {
        $bulan = date("m", strtotime($params['pengadaan_tgl_dist']));
        $tahun = date("Y", strtotime($params['pengadaan_tgl_dist']));
                
        $sql = "SELECT CAST(LEFT(pengadaan_ba_dist,4) AS UNSIGNED)+1 'new_number' FROM pengadaan";
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result(); 
            
            if(is_null($result['new_number'])){
                $nomor_ba = '0001/R.T/AMIKOM/'.$bulan.'/'.$tahun;
            }else{
                $nomor = '';
                for ($index=strlen($result['new_number']);$index<4;$index++) {
                    $nomor.='0';
                }
                $nomor.= $result['new_number'];
                $nomor_ba = $nomor.'/R.T/AMIKOM/'.$bulan.'/'.$tahun; 
            } 
            return $nomor_ba;
        } else {
            $nomor_ba = '0001/R.T/AMIKOM/'.$bulan.'/'.$tahun;
            return $nomor_ba;
        } 
    }
    
    function update_nomor_berita_serah_terima($params) { 
        $sql = "UPDATE pengadaan SET pengadaan_ba_dist = ?, pengadaan_tgl_dist = ? WHERE pengadaan_id = ?";
        return $this->db->query($sql, $params);
    }
    
    function get_tgl_berakhir($params) { 
         $sql = "SELECT DATE_ADD(?, INTERVAL ? YEAR)'tanggal'";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['tanggal'];
        } else {
            return '';
        }
     }
}
